﻿using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using System.Collections.Generic;
using NPOI.HSSF.Util;
using System.IO;
using System.Reflection;
using NPOI.XSSF.UserModel;
using System;
using System.Data;
using System.Data.Linq;
using Ext.Net;

namespace ExtSite
{
    public class ExcelHelper 
    {
        #region Constructor
        public ExcelHelper() { }

        #endregion


        #region Member Variables
        private int pageServerSize = 0;
        private string bookmarkKey = null;
        private string no;
        private ISheet m_sheet;
        private IWorkbook m_workbook;
        private DataTable _dt;
        public FileStream fs;
        #endregion


        // <summary>
        /// Mở file excel dựa theo tham số
        /// </summary>
        /// <param name="p_strSPname">FileFullName</param>
        /// <param name="p_arrParameter">Danh sách các tham số truyền vào</param>
        /// /// <param name="p_strSheetName">sheetName</param>
        /// <param name="p_arrParameter">Danh sách các tham số truyền vào</param>
        public bool OpenExcel(string fullFilePath)
        {
            try
            {
               //  fs = new FileStream(fullFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
                FileStream fs = new FileStream(fullFilePath, FileMode.Open, FileAccess.Read);

                m_workbook = new XSSFWorkbook(fs);

              //  m_workbook = new XSSFWorkbook(fullFilePath);
               
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public bool CloseExcel(string fullFilePath)
        {
            try
            {
                m_workbook = null;
              
                fs.Close();
                fs.Dispose();
              
              
               
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public bool OpenSheet(string sheetName)
        {
            try
            {

                m_sheet = m_workbook.GetSheet(sheetName);

                return true;
            }
            catch (Exception)
            {
                return false;
            }

        }

        public bool OpenSheet(int sheetIdx)
        {
            try
            {

                m_sheet = m_workbook.GetSheetAt(sheetIdx);

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }

        }

        public string GetSheetName(int sheetIdx)
        {
            return m_workbook.GetSheetName(sheetIdx);
        }

        public int GetLastSheetNum()
        {
            return m_workbook.NumberOfSheets;
        }
        public int ColumnNum(string ColumnName)
        {
            string _alphabet = "A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z;AA;AB;AC;AD;AE;AF;AG;AH;AI;AJ;AK;AL;AM;AN;AO;AP;AQ;AR;AS;AT;AU;AV;AW;AX;AY;AZ;";

            return _alphabet.Split(';').ToList().IndexOf(ColumnName.ToUpper());
        }
        public int GetLastCellNum()
        {
            IRow row = m_sheet.GetRow(0);
            return row.LastCellNum;
        }
        /// <summary>
        /// Lấy dữ liệu từ file Excel và chuyển thành DataTable
        /// </summary>
        public DataTable GetData(int headerRow, int startColumn, int endColumn, int startRow, int endRow)
        {
            try
            {
                IRow r = m_sheet.GetRow(headerRow);
                for (int i = startColumn; i <= endColumn; i++)
                {
                    _dt.Columns.Add(r.GetCell(i).ToString());
                }

                for (int i = startRow; i <= endRow; i++)
                {
                    IRow row = m_sheet.GetRow(i);
                    DataRow _dr = null;
                    int num = 0;
                    _dr = _dt.NewRow();

                    for (int j = startColumn; j <= endColumn; j++)
                    {
                        string s = row.GetCell(j).ToString();
                        _dr[num++] = s;
                    }

                    _dt.Rows.Add(_dr);
                }

                return _dt;
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// Lấy dữ liệu từ file Excel và chuyển thành DataTable
        /// </summary>
        public DataTable GetData(int headerRow, string startColumn, string endColumn, int startRow, int endRow)
        {
            try
            {
                int _startColumn, _endColumn;
                _startColumn = ColumnNum(startColumn.Substring(0, 1));
                _endColumn = ColumnNum(endColumn.Substring(0, 1));

                _dt = new DataTable();
                IRow r = m_sheet.GetRow(headerRow);
                for (int i = _startColumn; i <= r.Cells.Count - 1; i++)
                {
                    _dt.Columns.Add(r.GetCell(i).StringCellValue);
                }
                int index = startRow - 1;
                while (true)
                {

                  
                    string b = "{\\\"";
                    IRow row = m_sheet.GetRow(index);
                    DataRow _dr = null;
                    int num = 0;
                    _dr = _dt.NewRow();
                    if (row.GetCell(2) == null || row.GetCell(2).ToString()=="")
                        break;
                    for (int j = _startColumn; j <= r.Cells.Count - 1; j++)
                    {
                        object s = row.GetCell(j);
                        bool flag = true;
                        try
                        {
                            if(isNumber(s==null?"":s.ToString()))
                            {
                                _dr[num++] = double.Parse(s.ToString());
                            }
                            else
                            {
                                _dr[num++] = s.ToString();
                            }
                        }
                        catch( Exception ex )
                        {
                        }
                       
                    }

                   _dt.Rows.Add(_dr);
                    index++;
                }
             
                return _dt;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        
        
        private bool isNumber(string value)
        {
            bool result = false;
            try
            {
                double a = double.Parse(value);
                result= true;
            }
            catch (Exception)
            {
                result = false;
              
            }
            return result;
        }
        
   
        
        public string getErrorMessage(Exception ex)
        {
            if (ex.InnerException == null)
            {
                return ex.InnerException.Message.ToString();
            }
            else
            {
                return ex.Message.ToString();
            }
        }
        public string getValueCell(ICell s)
        {
            
            switch (s.CellType)
            {
                case CellType.NUMERIC:
                    return s.NumericCellValue.ToString();  
                case CellType.BOOLEAN:
                    return s.BooleanCellValue.ToString();
                case CellType.STRING:
                    return s.StringCellValue.ToString();
                 default:
                    return s.ToString();
                  

            }
        }
        public static string DecodeCustomFormat(string cellValue)
        {
            return cellValue.Replace(",", "").Replace("_* -??", "0").Replace("_*", "").Replace("_(* (", "-").Replace(")", "");
        }
        
    }
}
